﻿using System;
using System.Text;
using System.Data;
using System.Reflection;

namespace MWTools.Client.Common
{
    public class DataMappingUtility
    {
        public static string GenerateEntityTable(Type type, bool isCompact)
        {
            return GenerateEntityTable(type, isCompact, "");
        }

        public static string GenerateEntityTable(Type type, bool isCompact, string schema)
        {
            StringBuilder scriptWriter = new StringBuilder();

            if (schema.Length > 0)
                scriptWriter.AppendFormat("Create Table {1}.{0} ( \r\n", type.Name, schema);
            else
                scriptWriter.AppendFormat("Create Table {0} ( \r\n", type.Name);

            PropertyInfo[] properties = type.GetProperties();
            string columnName = "";

            for (int propertyIndex = 0; propertyIndex < properties.Length; propertyIndex++)
            {
                PropertyInfo columnProperty = properties[propertyIndex];

                columnName = columnProperty.Name;
                DataMappingAttribute dfa = Attribute.GetCustomAttribute(columnProperty, typeof(DataMappingAttribute)) as DataMappingAttribute;


                string typeInfo = "";

                if (dfa == null)
                {
                    Logger.WarnFormat("No Data Attribute for property:{0}", columnProperty.Name);
                    switch (columnProperty.PropertyType.ToString().ToUpper())
                    {
                        case "SYSTEM.DATETIME":
                            if (isCompact)
                                typeInfo = "datetime";
                            else
                                typeInfo = "smalldatetime";
                            break;
                        case "SYSTEM.INT32":
                            typeInfo = "INT";
                            break;
                        case "SYSTEM.INT64":
                            typeInfo = "BIGINT";
                            break;
                        case "SYSTEM.BOOLEAN":
                            typeInfo = "BIT";
                            break;
                        case "SYSTEM.STRING":
                            typeInfo = "NVARCHAR(255)";
                            break;
                        default:
                            typeInfo = "NVARCHAR(255)";
                            break;
                    }
                }
                else
                {
                    if (dfa.ColumnName != null)
                        columnName = dfa.ColumnName;

                    switch (dfa.DbType)
                    {
                        case DbType.Xml:
                            typeInfo = "xml";
                            break;
                        case DbType.DateTime:
                            if (isCompact)
                                typeInfo = "datetime";
                            else
                                typeInfo = "smalldatetime";

                            break;
                        case DbType.Int32:
                            typeInfo = "INT";
                            break;
                        case DbType.Int64:
                            typeInfo = "BIGINT";
                            break;
                        case DbType.LongText:
                            typeInfo = "NTEXT";
                            break;
                        case DbType.UniqueIdentifier:
                            typeInfo = "uniqueIdentifier";
                            break;
                        case DbType.Bit:
                            typeInfo = "BIT";
                            break;
                        case DbType.Text:
                            if (dfa.Length != 0)
                                typeInfo += string.Format("NVARCHAR({0})", dfa.Length.ToString());
                            else
                                typeInfo = "NVARCHAR(255)";
                            break;
                        default:
                            throw new ArgumentException(dfa.DbType.ToString());
                    }

                    if (dfa.IsPrimaryKey)
                    {
                        typeInfo += " PRIMARY KEY ";
                    }
                    else
                    {
                        if (dfa.IsRequired)
                            typeInfo += " NOT NULL";
                        else
                            typeInfo += " NULL";
                    }
                }

                scriptWriter.AppendFormat("[{0}] {1}, \r\n", columnName, typeInfo);

            }

            scriptWriter.Remove(scriptWriter.ToString().LastIndexOf(", \r\n"), ", \r\n".Length);
            scriptWriter.Append(");\r\n");

            return scriptWriter.ToString();
            //try
            //{

            //    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["defaultConnection"].ConnectionString))
            //    {
            //        Logger.DebugFormat("executing {0}", scriptWriter.ToString());
            //        SqlCommand cmd = new SqlCommand(scriptWriter.ToString(), connection);
            //        connection.Open();
            //        cmd.ExecuteNonQuery();
            //    }

            //}
            //catch (Exception  ex)
            //{
            //    Logger.FatalFormat(typeof(DataMappingUtility), "{0}", ex.Message);
            //}
        }

        public static string GenerateUpdateSqlFromInstance<T>(T item)
        {
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("[\x00-\x1F]");

            StringBuilder sbInsert = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            sbInsert.AppendFormat("Update {0} SET \r\n", item.GetType().Name);
            PropertyInfo[] properties = item.GetType().GetProperties();

            int valuesWritten = 0;

            for (int propertyIndex = 0; propertyIndex < properties.Length; propertyIndex++)
            {
                try
                {
                    PropertyInfo pi = properties[propertyIndex];

                    DataMappingAttribute dfa = Attribute.GetCustomAttribute(pi, typeof(DataMappingAttribute)) as DataMappingAttribute;

                    if (dfa != null && dfa.IsPrimaryKey)
                        continue;

                    if (dfa == null)
                        continue;


                    object propValue = pi.GetValue(item, null);

                    if (propValue == null || (string)propValue == "")
                        continue;

                    string literalValue = "";

                    switch (propValue.GetType().ToString().ToUpper())
                    {
                        case "SYSTEM.INT64":
                            literalValue = string.Format("{0}", Convert.ToInt64(propValue));
                            break;
                        case "SYSTEM.INT32":
                            literalValue = string.Format("{0}", Convert.ToInt32(propValue));
                            break;
                        case "SYSTEM.DATETIME":
                            DateTime dt = (DateTime)propValue;

                            if (dt.Year > 2050)
                                continue;
                            literalValue = string.Format("'{0}'", dt.ToString("yyyy-MM-ddTHH:mm:ss"));
                            break;
                        case "SYSTEM.BOOLEAN":
                            literalValue = string.Format("{0}", Convert.ToInt32(propValue));
                            break;
                        default:
                            literalValue = string.Format("'{0}'", Convert.ToString(propValue).Replace("'", "''"));
                            break;
                    }

                    string columnName = (dfa != null && dfa.ColumnName != null) ? dfa.ColumnName : pi.Name;

                    if (valuesWritten > 0)
                        sbInsert.Append(",");

                    sbInsert.AppendFormat("{0} = {1}", columnName, literalValue);
                    valuesWritten++;
                }
                catch (Exception ex)
                {
                    Logger.Fatal(typeof(DataMappingUtility), ex);
                }
            }


            string sql = regex.Replace(sbInsert.ToString() + sbValues.ToString(), "");
            Logger.DebugFormat("completed sql:{0}", sql);
            return sql;
        }

        private static string CheckString(PropertyInfo pi, DataMappingAttribute dfa, string literalValue)
        {
            literalValue = System.Text.RegularExpressions.Regex.Replace(literalValue, "[\\'\"\x00\x08\x0B\x0C\x0E-\x1F]", "");

            if (dfa.Length > 0 && literalValue.Length > dfa.Length)
            { //System.Diagnostics.Debug.Assert();
                Logger.WarnFormat("DataMappingUtility.GenerateInsertSqlFromInstance", "field:{0} was truncated - value:{1}", pi.Name, literalValue);

                literalValue = literalValue.Substring(0, dfa.Length - 2);
            }

            return literalValue;
        }
        public static string GenerateInsertSqlFromInstance<T>(T item)
        {
            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("[\x00-\x1F]");

            StringBuilder sbUpdateColumns = new StringBuilder();
            StringBuilder sbWhereCondition = new StringBuilder();
            sbUpdateColumns.AppendFormat("INSERT INTO sync.{0}(", item.GetType().Name);
            sbWhereCondition.AppendFormat(") VALUES (");
            PropertyInfo[] properties = item.GetType().GetProperties();

            int valuesWritten = 0;

            for (int propertyIndex = 0; propertyIndex < properties.Length; propertyIndex++)
            {
                try
                {
                    PropertyInfo pi = properties[propertyIndex];

                    DataMappingAttribute dfa = Attribute.GetCustomAttribute(pi, typeof(DataMappingAttribute)) as DataMappingAttribute;

                    if (dfa != null && dfa.IsPrimaryKey)
                        continue;

                    if (dfa == null)
                        continue;



                    object propValue = pi.GetValue(item, null);

                    if (propValue == null || (propValue is string && (string)propValue == ""))
                        continue;

                    string literalValue = "";

                    switch (propValue.GetType().ToString().ToUpper())
                    {
                        case "SYSTEM.INT64":
                            literalValue = string.Format("{0}", Convert.ToInt64(propValue));
                            break;
                        case "SYSTEM.INT32":
                            literalValue = string.Format("{0}", Convert.ToInt32(propValue));
                            break;
                        case "SYSTEM.DATETIME":
                            DateTime dt = (DateTime)propValue;

                            if (dt.Year > 2050 || dt.Year < 1950)
                                continue;
                            literalValue = string.Format("'{0}'", dt.ToString("yyyy-MM-dd"));
                            break;
                        case "SYSTEM.BOOLEAN":
                            literalValue = string.Format("{0}", Convert.ToInt32(propValue));
                            break;
                        default:
                            if (pi.PropertyType.BaseType.Name.ToUpper() == "ENUM")
                            {
                                literalValue = string.Format("{0}", Convert.ToInt32(propValue));
                                break;
                            }
                            literalValue = string.Format("'{0}'", CheckString(pi, dfa, Convert.ToString(propValue)));

                            break;
                    }

                    string columnName = (dfa != null && dfa.ColumnName != null) ? dfa.ColumnName : pi.Name;

                    if (dfa.IsPrimaryKey)
                        sbWhereCondition.AppendFormat(" WHERE {0} = {1}", literalValue);

                    if (valuesWritten > 0)
                    {
                        sbUpdateColumns.Append(",");
                        sbWhereCondition.Append(",");
                    }

                    sbUpdateColumns.AppendFormat("[{0}]\r\n", columnName);
                    sbWhereCondition.AppendFormat("{0}\r\n", literalValue);
                    valuesWritten++;
                }
                catch (Exception ex)
                {
                    Logger.Fatal(typeof(DataMappingUtility), ex);
                }


            }

            string sql = regex.Replace(String.Format("{0}{1})", sbUpdateColumns, sbWhereCondition), "");
            Logger.DebugFormat("completed sql:{0}", sql);
            return sql;
        }


        public static T FromDataRow<T>(DataRow dataRow) where T : new()
        {
            T item = new T();
            PropertyInfo[] properties = null;

            if (properties == null)
                properties = item.GetType().GetProperties();

            for (int propertyIndex = 0; propertyIndex < properties.Length; propertyIndex++)
            {
                string columnName = String.Empty;
                string propertyType = String.Empty;

                try
                {
                    PropertyInfo pi = properties[propertyIndex];

                    if (!pi.CanWrite)
                        continue;

                    DataMappingAttribute dfa = Attribute.GetCustomAttribute(pi, typeof(DataMappingAttribute)) as DataMappingAttribute;

                    if (dfa == null)
                        continue;

                    columnName = dfa.ColumnName ?? pi.Name;

                    if (dataRow.Table.Columns[columnName] == null || dataRow[columnName] == DBNull.Value)
                        continue;


                    
                    if (pi.CanWrite == false)
                        continue;

 
                   // Common.Logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "parsing " + columnName);
                    propertyType = pi.PropertyType.ToString().ToUpper();
                    switch (propertyType)
                    {
                        case "SYSTEM.INT64":
                            pi.SetValue(item, Convert.ToInt64(dataRow[columnName]), null);
                            break;
                        case "SYSTEM.UINT64":
                            pi.SetValue(item, Convert.ToUInt64(dataRow[columnName]), null);
                            break;
                        case "SYSTEM.INT32":
                            pi.SetValue(item, Convert.ToInt32(dataRow[columnName]), null);
                            break;
                        case "SYSTEM.DATETIME":
                            pi.SetValue(item, Convert.ToDateTime(dataRow[columnName]), null);
                            break;
                        case "SYSTEM.BOOLEAN":
                            pi.SetValue(item, Convert.ToBoolean(dataRow[columnName]), null);
                            break;
                        case "SYSTEM.STRING":
                            pi.SetValue(item, Convert.ToString(dataRow[columnName]), null);
                            break;
                        case "SYSTEM.GUID":
                            pi.SetValue(item, new Guid(dataRow[columnName].ToString()), null);
                            break;
                        default:
                            if (pi.PropertyType.BaseType.Name.ToUpper() == "ENUM")
                            {

                                pi.SetValue(item, System.Enum.Parse(pi.PropertyType, dataRow[columnName].ToString()), null);
                                break;
                            }
                            pi.SetValue(item, Convert.ToString(dataRow[columnName]), null);
                            break;
                    }
                }
                catch (Exception ex)
                {
                    Logger.DebugFormat("FromDataRow", "error converting {0} to {1}", columnName, propertyType);
                    
                    //  throw;
                }

            }

            return item;
        } // convertDataTableToList(...)
    }
}
